﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;
using Follow.Common;

namespace Follow.DataAccess
{
    public class FollowupDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public FollowupDAO()
        {
            conn = ConnectionUtils.getConnection();
        }

        public IList<Funcionario> findByFunc(Int32 id)
        {
            IList<Funcionario> listafunc = new List<Funcionario>();
            try
            {
                conn.Open();
                String sqlCommand = " select * from followup where funcionario_idfuncionario = " + id;
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Funcionario f = new Funcionario();
                    f.IDFuncionario = reader.GetInt32(0);
                    listafunc.Add(f);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return listafunc;
        }

        public IList<Contato> findByContato(Int32 id)
        {
            IList<Contato> listacontato = new List<Contato>();
            try
            {
                conn.Open();
                String sqlCommand = " select * from followup where contato_idcontato = " + id;
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Contato c = new Contato();
                    c.IDContato = reader.GetInt32(0);
                    listacontato.Add(c);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return listacontato;
        }


        public IList<FollowUp> findByProposta(Int32 id)
        {
            IList<FollowUp> listafollow = new List<FollowUp>();
            try
            {
                conn.Open();
                String sqlCommand = " select * from followup where proposta_idproposta = " + id;
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    FollowUp follow = new FollowUp();
                    follow.IDProposta = reader.GetInt32(0);
                    listafollow.Add(follow);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return listafollow;
        }
        
        public void insert(FollowUp follow, int idUsuario)
        {
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " insert into followup (contato_idcontato, statusfollow_idstatus, funcionario_idfuncionario, proposta_idproposta, datafollowup, observacoes) values " +
                                        " (@contato_idcontato, @statusfollow_idstatus, @funcionario_idfuncionario, @proposta_idproposta, @datafollowup, @observacoes) ";
                    cmd = new SqlCommand(sqlCommand, conn);

                    if (follow.IDContato == Int32.MinValue)
                    {
                        cmd.Parameters.Add(new SqlParameter("contato_idcontato", DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("contato_idcontato", follow.IDContato));
                    }

                    if (follow.IDFuncionario == Int32.MinValue)
                    {
                        cmd.Parameters.Add(new SqlParameter("funcionario_idfuncionario", DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("funcionario_idfuncionario", follow.IDFuncionario));
                    }

    
                    cmd.Parameters.Add(new SqlParameter("statusfollow_idstatus", follow.IDStatus));
                    cmd.Parameters.Add(new SqlParameter("proposta_idproposta", follow.IDProposta));
                    cmd.Parameters.Add(new SqlParameter("datafollowup", follow.DataFollowUp));
                    cmd.Parameters.Add(new SqlParameter("observacoes", follow.ObsFollowUp));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, SQLStringSingleton.GetCommandText(cmd), "Insert", "FollowUp");
        }

    }
       


}